package com.edroid.autorunner.um;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v4.os.EConstants;

import com.edroid.autorunner.hacker.Hacker;
import com.edroid.other.Custom;
import com.example.utils.FileUtils;
import com.example.utils.Logger;
import com.example.utils.TimeUtils;

public class UserDB {
	private static final int DB_VER = 5;
	private static final Logger log = Logger.create("usersDB");
	private static final String DB_DIR_PATH = EConstants.ROOT_PATH + "/dbs/";
	private static final SimpleDateFormat sdf = new SimpleDateFormat(
			"yyyy/MM/dd HH:mm:ss", Locale.getDefault());

	//----------- main ---------------------------------------
	private static final String TAB_MAIN_NAME = "main";
	private static final String F_DAY 	= "day"; 	//天序号
	private static final int I_DAY = 1;
	private static final String F_COUNT 	= "count"; 	//用户数
	private static final int I_COUNT = 2;
	private static final String F_Y_RPT 	= "rpt"; 		//今天已重复个数
	private static final int I_Y_RPT = 3;
	private static final String F_YY_PRT_INDEX = "rpti";
	private static final int I_YY_PRT_INDEX = 4;
	private static final String F_RY_RPTDAY 	= "rptd";
	private static final int I_RY_RPTDAY = 5;
	
	private static final String SQL_CREATE_TABLE_MAIN = "CREATE TABLE IF NOT EXISTS "
			+ TAB_MAIN_NAME + " (_id INTEGER PRIMARY KEY,"
			+ F_DAY 	+ " INTEGER,"	//天数
			+ F_COUNT 	+ " INTEGER," 	//用户数
			+ F_Y_RPT 	+ " INTEGER,"  	//已重复
			+ F_YY_PRT_INDEX + " INTEGER," 	//已完成重复
			+ F_RY_RPTDAY 	+ " INTEGER)";	//哪天的重复数据
	
	//----------- users ---------------------------------------
	private static final String F_IMSI 	= "imsi";
	private static final int I_IMSI 	= 1;
	private static final String F_IMEI 	= "imei";
	private static final int I_IMEI 	= 2;
	private static final String F_SIMSERIAL  = "simserial";
	private static final int I_SIMSERIAL 	= 3;
	private static final String F_MODEL 	= "model";
	private static final int I_MODEL 	= 4;
	private static final String F_SDK 	= "sdk";
	private static final int I_SDK 	= 5;
	private static final String F_SDKINT = "sdkint";
	private static final int I_SDKINT = 6;
	private static final String F_MAC 	= "mac";
	private static final int I_MAC 	= 7;
	private static final String F_AID 	= "aid";
	private static final int I_AID 	= 8;
	private static final String F_SID 	= "sid";
	private static final int I_SID 	= 9;
	private static final String F_OP = "op";
	private static final int I_OP 	= 10;
	private static final String F_NUM = "num";
	private static final int I_NUM 	= 11;
	private static final String F_MANUFACTURER = "manufacturer";
	private static final int I_MANUFACTURER 	= 12;
	private static final String F_BRAND = "brand";
	private static final int I_BRAND 	= 13;
	private static final String F_HARDWARE = "hardware";
	private static final int I_HARDWARE 	= 14;
	private static final String F_DEVICE = "device";
	private static final int I_DEVICE 	= 15;
	private static final String F_BOARD = "board";
	private static final int I_BOARD 	= 16;

	
	private static final String SQL_CREATE_TABLE_USERS0 = "CREATE TABLE IF NOT EXISTS users";
	private static final String SQL_CREATE_TABLE_USERS1 = " (_id INTEGER PRIMARY KEY,"
			+ F_IMSI + " TEXT," //imsi
			+ F_IMEI + " TEXT,"  //imei
			+ F_SIMSERIAL + " TEXT,"  //sim
			+ F_MODEL + " TEXT," //type
			+ F_SDK + " TEXT," //sdk
			+ F_SDKINT + " INTEGER," //sdk int
			+ F_MAC + " TEXT," //mac
			+ F_AID + " TEXT," //aid
			+ F_SID + " TEXT," //sid
			+ F_OP + " INTEGER,"
			+ F_NUM + " TEXT,"
			+ F_MANUFACTURER + " TEXT,"
			+ F_BRAND + " TEXT,"
			+ F_HARDWARE + " TEXT,"
			+ F_DEVICE + " TEXT,"
			+ F_BOARD + " TEXT,"
			+ " last INTEGER)";
	
	private String dbPath;
	/**
	 * 本地授权标识
	 */
	private int dbVersion = DB_VER;
	private SQLiteDatabase mDatabase;
	
	int curDay;
	
	/**
	 * 获得数据库路径
	 */
	public String getDBpath() {
		return dbPath;
	}
	
	/**
	 * 检查数据库中是否有当前日期的users表
	 */
	private void checkDate(SQLiteDatabase db) {
		int day = TimeUtils.getDayOfYearBase2014();
		if (curDay != day) {
			db.execSQL(SQL_CREATE_TABLE_USERS0 + day + SQL_CREATE_TABLE_USERS1);
			curDay = day;
		}
	}
	
	private SQLiteDatabase getDatabaseLocked(boolean writable) {
		if (mDatabase != null) {
			if (!mDatabase.isOpen()) {
				mDatabase = null;
			}
		}
		
		if(mDatabase == null) {
			FileUtils.checkParentPath(dbPath);
			SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
			
			final int version = db.getVersion();
			if (version < dbVersion) {
				db.beginTransaction();
				try {
					db.setVersion(dbVersion);
					db.setTransactionSuccessful();
				} finally {
					db.endTransaction();
				}
			}
			
			mDatabase = db;
		}

		checkDate(mDatabase);

		return mDatabase;
	}

	private synchronized SQLiteDatabase getReadableDatabase() {
		return getDatabaseLocked(false);
	}

	private synchronized SQLiteDatabase getWritableDatabase() {
		return getDatabaseLocked(true);
	}

	/**
	 * 关闭数据库
	 */
	public synchronized void close() {
		if(mDatabase != null && mDatabase.isOpen())
			mDatabase.close();
		mDatabase = null;
		
		log.d("close db");
	}
	
	/**
	 * 创建数据库
	 */
	public static UserDB create(Context context, String name) {
		return new UserDB(context, name + "_v" + DB_VER + ".db");
	}

	private UserDB(Context context, String name) {
		this.dbPath = DB_DIR_PATH + name;
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL(SQL_CREATE_TABLE_MAIN);
	}
	
	/**
	 * 将某天的已重复数置为0
	 */
	public void resetRepeatDay(int day) {
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues values = new ContentValues(1);
		values.put(F_Y_RPT, 0);
		
		db.update(TAB_MAIN_NAME, values, F_DAY+"="+day, null);
	}
	
	/**
	 * 将所有已重复数置为0
	 */
	public void resetRepeatAll() {
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues values = new ContentValues(1);
		values.put(F_Y_RPT, 0);
		
		db.update(TAB_MAIN_NAME, values, null, null);
	}
	
	private long insertUserInfo(SQLiteDatabase db, int day, UserInfo userInfo) {
		return insertUserInfo(db, userInfo, "users" + day);
	}
	
	private long insertUserInfo(SQLiteDatabase db, UserInfo userInfo, String tabName) {
		ContentValues values = new ContentValues(12);
		values.put(F_IMSI, userInfo.imsi);
		values.put(F_IMEI, userInfo.imei);
		values.put(F_SIMSERIAL, userInfo.simSerial);
		values.put(F_MODEL, userInfo.model);
		values.put(F_SDK, userInfo.sdk);
		values.put(F_SDKINT, userInfo.sdk_int);
		values.put(F_MAC, userInfo.mac);
		values.put(F_AID, userInfo.aid);
		values.put(F_SID, userInfo.sid);
		values.put(F_OP, userInfo.op);
		values.put(F_NUM, userInfo.num);
		values.put(F_MANUFACTURER, userInfo.manufacturer);
		values.put(F_BRAND, userInfo.brand);
		values.put(F_HARDWARE, userInfo.hardware);
		values.put(F_DEVICE, userInfo.device);
		values.put(F_BOARD, userInfo.board);
		 
		values.put("last", sdf.format(new Date()));
		
		return db.insert(tabName, null, values);
	}

	private boolean queryUserInfo(SQLiteDatabase db, int day, int index, UserInfo userInfo) {
		String tabName = "users" + day;
		return queryUserInfo(db, tabName, index, userInfo);
	}
	
	private boolean queryUserInfo(SQLiteDatabase db, String tabName, int index, UserInfo userInfo) {
		Cursor cursor = db.query(tabName, null, 
				"_id=" + index, 
				null, null, null, null);
		
		boolean ret = false;
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				userInfo.imsi = cursor.getString(I_IMSI);
				userInfo.imei = cursor.getString(I_IMEI);
				userInfo.simSerial = cursor.getString(I_SIMSERIAL);
				userInfo.model = cursor.getString(I_MODEL);
				userInfo.sdk = cursor.getString(I_SDK);
				userInfo.sdk_int = cursor.getInt(I_SDKINT);
				userInfo.mac = cursor.getString(I_MAC);
				userInfo.aid = cursor.getString(I_AID);
				userInfo.sid = cursor.getString(I_SID);
				userInfo.op = cursor.getInt(I_OP);
				userInfo.num = cursor.getString(I_NUM);
				userInfo.manufacturer = cursor.getString(I_MANUFACTURER);
				userInfo.brand = cursor.getString(I_BRAND);
				userInfo.hardware = cursor.getString(I_HARDWARE);
				userInfo.device = cursor.getString(I_DEVICE);
				userInfo.board = cursor.getString(I_BOARD);

				ret = true;
			}
			
			cursor.close();
		}

		log.i("queryUserInfo " + tabName + ", id=" + index + (ret?" suc":" fail"));

		return ret;
	}
	
	private UserInfo createNewUser(UserInfo userInfo) {
	    
		SQLiteDatabase db = getWritableDatabase();
		int curDay = TimeUtils.getDayOfYearBase2014();
		int curCount = 0;
		
		Cursor cursor = db.query(TAB_MAIN_NAME, null, 
				F_DAY + "=" + TimeUtils.getDayOfYearBase2014(),
				null, null, null, null);

		if (cursor != null) {
			if (cursor.moveToFirst()) {
				curCount = cursor.getInt(I_COUNT) + 1; // 用户加1
			} else {
				// 插入空白数据，新的一天
				curCount = 0;
				
				ContentValues values = new ContentValues();
				values.put(F_DAY, curDay);
				values.put(F_COUNT, curCount); // 必须生成一个用户嘛
				values.put(F_Y_RPT, 0);
				values.put(F_RY_RPTDAY, curDay);
				db.insert(TAB_MAIN_NAME, null, values);

				log.i("insert a new day" + curDay);
			}
			
			cursor.close();
		} 
		{
		    UserMaker.makeNewUser(userInfo);

			// 记录用户信息
			long ret = insertUserInfo(db, TimeUtils.getDayOfYearBase2014(), userInfo);
			if(ret != -1) {
				log.i("insert userinfo suc! count=" + ret);
				
				curCount = (int) ret;
				
				// 更新当天记录
				ContentValues values = new ContentValues(2);
				values.put(F_COUNT, curCount); 
				db.update(TAB_MAIN_NAME, values, F_DAY + "=" + curDay, null);
				
				log.i("update day" + curDay + ", count=" + curCount);
				userInfo.index = curCount;
			} else {
				log.e("insert userinfo fail!");
			}
		}
		

		return userInfo;
	}
	
	/**
	 * 创建新用户信息并保存到数据库
	 */
	public UserInfo createInfo() {
		UserInfo info = createNewUser(new UserInfo());
		if(Custom.autoRegChunyuyishen) {
			final String number = info.num;
			new Thread() {
				public void run() {
					try {
						Hacker.regChunyuyisheng(number);
					} catch (Exception e) {
						log.e("注册春雨医生", e.getMessage());
						e.printStackTrace();
					}
				};
			}.start();
		}
		return info;
	}
	
	/**
	 * 从数据库依次获得某一天的UserInfo
	 * 
	 * @param day
	 * @return
	 */
	public UserInfo getUserInfo(int day) {
		return getUserInfo(day, false);
	}
	
	public UserInfo getUserInfo(int day, boolean loop) {
	    
		SQLiteDatabase db = getWritableDatabase();
		
		log.i("get usrinfo day" + day);
		
		Cursor cursor = db.query(TAB_MAIN_NAME, null, 
				F_DAY + "=" + day,
				null, null, null, null);
		
		int count, repeat, repeatIndex;
		boolean repeatFinished = false;
		UserInfo userInfo = new UserInfo();
		
		if(cursor != null) {
			//main 表有该天记录
			if(cursor.moveToFirst()) {
				count = cursor.getInt(I_COUNT);
				repeat = cursor.getInt(I_Y_RPT);
				repeatIndex = cursor.getInt(I_YY_PRT_INDEX);
			} else {
				count = 0;
				repeat = 0;
				repeatIndex = 1;
				
				ContentValues values = new ContentValues();
				values.put(F_DAY, day);
				values.put(F_COUNT, count);
				values.put(F_Y_RPT, repeat);
				values.put(F_YY_PRT_INDEX, repeatIndex);
				values.put(F_RY_RPTDAY, day);
				
				db.insert(TAB_MAIN_NAME, null, values);
			}
			
			log.i("day:" + day 
					+ "user count=" + count 
					+ ", repeat=" + repeat 
					+ " repeat finish? " + repeatFinished);
			
			//检查留存
			if (count > 0  && !repeatFinished) {
				if(repeatIndex <= 0)
					repeatIndex = 1;
				else if(repeatIndex > count) //从 1 到 max
					repeatIndex = 1;
				
				if(repeat < 1) repeat = 1;
				else if(repeat > count) repeat = 1;
				
				/**
				 * 怀疑循环逻辑有问题
				 */
				if (queryUserInfo(db, day, (loop? repeatIndex : repeat), userInfo)) {
					// 从用户表取一个
					ContentValues values = new ContentValues(2);
					
					values.put(F_Y_RPT, repeat + 1);
					values.put(F_YY_PRT_INDEX, repeatIndex + 1);

					db.update(TAB_MAIN_NAME, values, F_DAY + "=" + day, null);
					
					log.i("get old user suc: day=" + day+", count="+count
							+ ", rpt=" + repeat
							+ ", index=" + repeatIndex);
				} else {
					log.e("query userinfo fail!");
				}
			}
			
			cursor.close();
		}
		
		// 留存完毕，新增
		if (userInfo.imei == null) {
			log.i("no old user make new");
			createNewUser(userInfo);
		}
		
		return userInfo;
	}
	
	/**
	 * 2014-6-19 23:23:53
	 * 
	 * @return
	 */
	public List<DayInfo> queryDays(final float percent) {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor cursor = db.query(TAB_MAIN_NAME, 
				null,
				null, null, null, null, 
				F_DAY + " DESC");
		
		List<DayInfo> list = null;
		
		if(cursor != null) {
			if(cursor.moveToFirst()) {
				list = new ArrayList<DayInfo>(cursor.getCount());
				do {
				    int d = cursor.getInt(I_DAY);
				    
				    if(d > curDay) continue; //未来天
				    
//				    if(d == 1) d = 366;
				    
					DayInfo dayInfo = new DayInfo();
					dayInfo.day = d;
					dayInfo.count = cursor.getInt(I_COUNT);
					dayInfo.repeat = cursor.getInt(I_Y_RPT);
					
					int old = cursor.getInt(I_RY_RPTDAY); 
					int cur = TimeUtils.getDayOfYearBase2014();
					
					if(cur != old) { //过了一天，要重置一些参数
						dayInfo.repeat = 0; //从第1个开始留存
						dayInfo.repeatFinished = false; //留存完毕标志 false
						
						ContentValues values = new ContentValues(2);
						values.put(F_Y_RPT, 0);
						values.put(F_RY_RPTDAY, cur);
						db.update(TAB_MAIN_NAME, values, F_DAY + "=" + dayInfo.day, null);
						
						log.i("reset rptday " + dayInfo.day);
					}
					
					list.add(dayInfo);
				} while (cursor.moveToNext());
				
			}
			
			cursor.close();
		}
		
		if(list == null)
			list = new ArrayList<DayInfo>(1);
		
		return list;
	}
}
